package com.iSnail.ms.gather.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import com.iSnail.ms.gather.db.DBManager;
import com.iSnail.ms.gather.entity.Gather;

public class GatherDao {
	private static String insertsql = "insert into gather(name) values(?)";
	private static String queryallsql = "select gatherid,name from gather";
	private static String queryidsql = queryallsql + " where gatherid=?";
	private static String querynamesql = queryallsql + " where name=?";

	private static String delall = "delete from gather";
	private static String delbyid = delall + " where gatherid=?";
	private static String delbyname = delall + " where name=?";

	private static String updatesql = "update gather set name=? where gatherid=?";

	public Gather add(Gather entity) throws Exception {
		Connection con = null;
		PreparedStatement psm = null;
		try {
			con = DBManager.getCon();
			psm = DBManager.getPSM(con, insertsql);
			psm.setString(1, entity.getName());
			psm.execute();
			return getGatherByName(entity.getName());
		} finally {
			DBManager.closeDbSource(null, psm, con);
		}
	}

	public boolean deletebyid(int id) throws Exception {
		Connection con = null;
		PreparedStatement psm = null;
		try {
			con = DBManager.getCon();
			psm = DBManager.getPSM(con, delbyid);
			psm.setInt(1, id);
			return psm.execute();
		} finally {
			DBManager.closeDbSource(null, psm, con);
		}
	}
	
	public boolean deletebyname(String name) throws Exception {
		Connection con = null;
		PreparedStatement psm = null;
		try {
			con = DBManager.getCon();
			psm = DBManager.getPSM(con, delbyname);
			psm.setString(1, name);
			return psm.execute();
		} finally {
			DBManager.closeDbSource(null, psm, con);
		}
	}

	public boolean modify(String newname,int id) throws Exception {
		Connection con = null;
		PreparedStatement psm = null;
		try {
			con = DBManager.getCon();
			psm = DBManager.getPSM(con, updatesql);
			psm.setString(1, newname);
			psm.setInt(2, id);
			return psm.execute();
		} finally {
			DBManager.closeDbSource(null, psm, con);
		}
	}

	public ArrayList<Gather> queryAllGather() throws Exception {
		ArrayList<Gather> result = new ArrayList<Gather>();
		Connection con = null;
		Statement sm = null;
		ResultSet rs = null;
		Gather entity = null;
		try {
			con = DBManager.getCon();
			sm = DBManager.getSM(con);
			rs = sm.executeQuery(queryallsql);
			while (rs.next()) {
				entity = new Gather();
				entity.setId(rs.getInt(1));
				entity.setName(rs.getString(2));
				result.add(entity);
			}
			return result;
		} finally {
			DBManager.closeDbSource(null, sm, con);
		}
	}

	public Gather getGatherByID(int id) throws Exception {
		Connection con = null;
		PreparedStatement psm = null;
		ResultSet rs = null;
		Gather result = null;
		try {
			con = DBManager.getCon();
			psm = DBManager.getPSM(con, queryidsql);
			psm.setInt(1, id);
			rs = psm.executeQuery();
			while (rs.next()) {
				result = new Gather();
				result.setId(rs.getInt(1));
				result.setName(rs.getString(2));
			}
			return result;
		} finally {
			DBManager.closeDbSource(null, psm, con);
		}
	}

	public Gather getGatherByName(String name) throws Exception {
		Connection con = null;
		PreparedStatement psm = null;
		ResultSet rs = null;
		Gather result = null;
		try {
			con = DBManager.getCon();
			psm = DBManager.getPSM(con, querynamesql);
			psm.setString(1, name);
			rs = psm.executeQuery();
			while (rs.next()) {
				result = new Gather();
				result.setId(rs.getInt(1));
				result.setName(rs.getString(2));
			}
			return result;
		} finally {
			DBManager.closeDbSource(null, psm, con);
		}
	}
	
	public static void main(String[] args) throws Exception {
		GatherDao dao = new GatherDao();
		Gather bo = new Gather("iSnail");
		bo = dao.add(bo);
		System.out.println(bo.getId());
		
		dao.modify("fuck",bo.getId());
		dao.deletebyid(4);
	}
}